SUPPORT / SAMPLES & SAS NOTES
Problem Note 65682: Running FedSQL with an Oracle table is slow, even when you use a LIMIT clause
 |  |  |  |  |  |
When you query an Oracle table and use the LIMIT clause using either SAS® Federation Server or FedSQL, a row limit is not passed to the database. In this scenario, a select * from table SQL statement is passed to the database. Instead, SAS should pass the select * from table where rownum <= limit_number SQL statement to Oracle. This behavior is causing a performance issue.
For example, you would encounter this issue if you submitted code similar to the following:
%let constr = "DRIVER=SQL;CONOPTS=(driver=oracle;catalog=ora;path=orapath;uid=""xxxx"";pwd=yyyyy)";
proc fedsql noerrorstop nolibs noprompt=&constr number;
drop table sas_test;
create table sas_test(char_column char(255));
insert into sas_test values('charcol1');
quit;
proc fedsql noerrorstop nolibs noprompt=&constr number iptrace;
select * from sas_test limit 1;
quit;
proc fedsql noerrorstop nolibs noprompt=&constr number;
drop table sas_test;
create table sas_test(char_column char(255));
insert into sas_test values('charcol1');
quit;
proc fedsql noerrorstop nolibs noprompt=&constr number iptrace;
select * from sas_test limit 1;
quit;
Note: In the code above, you will see your connection information for path, uid, and pwd.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Federation Server | Solaris for x64 | 4.2 | 4.2 | 9.4 TS1M3 | 9.4 TS1M6 |
| Linux for x64 | 4.2 | 4.2 | 9.4 TS1M3 | 9.4 TS1M6 | ||
| 64-bit Enabled Solaris | 4.2 | 4.2 | 9.4 TS1M3 | 9.4 TS1M6 | ||
| 64-bit Enabled AIX | 4.2 | 4.2 | 9.4 TS1M3 | 9.4 TS1M6 | ||
| Microsoft® Windows® for x64 | 4.2 | 4.2 | 9.4 TS1M3 | 9.4 TS1M6 | ||